﻿Partial Public Class DTSDeploy
#Region "Global Declares"
    Private __oNCLDTSJobs As New NCLDTSJobs
    Private __oSQL As Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase

    Private __sMessage As String
    Private __sConnString As String
    Private __sDBSrvName As String
    Private __sDBUserName As String
    Private __sDBPassword As String
    Private __sDBTrustedConn As Boolean
    Private __sPkgListFilePath As String
    Private __sPkgListFileName As String
#End Region

#Region "Process Routines"
    Private Function DeployList() As Boolean
        Dim _fOk As Boolean

        Try
            Dim _oNCLDTSJob As NCLDTSJob

            If __oNCLDTSJobs Is Nothing Then
                lbMessage.Items.Add("No DTS Jobs to Deploy")
                Return False
            End If

            _fOk = ValidateDTSFileList()

            If _fOk = False Then
                lbMessage.Items.Add("Expected DTS Files not available, cannot deploy.")
                Return False
            End If

            lbMessage.Items.Add("Deployment Starting...")

            For Each _oNCLDTSJob In __oNCLDTSJobs
                _fOk = DeployDTSJob(_oNCLDTSJob.DTSFileNameWPath)
                
                If _fOk = True Then
                    lbMessage.Items.Add("Success - DTS Job: " & _oNCLDTSJob.DTSFileName)
                Else
                    lbMessage.Items.Add(">>>Fail - DTS Job: " & _oNCLDTSJob.DTSFileName)
                End If

                'Relinquish control back to OS
                System.Windows.Forms.Application.DoEvents()
            Next

            lbMessage.Items.Add("Deployment Finished.")

            Return True
        Catch ex As Exception
            MsgBox("Exception Raised in DeployList: " & ex.ToString, MsgBoxStyle.OkOnly, "Exception Raised")
            Return False
        End Try
    End Function

    Private Function GetDeployList(ByVal PkgListFilePath As String, ByVal PkgListFileName As String) As Boolean
        Try
            Dim _oXML As New Chilkat.Xml

            'Clear the list
            __oNCLDTSJobs.Clear()

            'Condition the File Path
            PkgListFilePath = CondFilePath(PkgListFilePath)

            'Load the XML File
            _oXML.LoadXmlFile2(PkgListFilePath & PkgListFileName, True)

            While Not (_oXML Is Nothing)
                Select Case UCase(_oXML.Tag)
                    Case "DTSDEPLOYLIST"
                        ParseDTSDEPLOYLIST(_oXML, PkgListFilePath)
                End Select

                _oXML = _oXML.NextSibling()
            End While

            Return True
        Catch ex As Exception
            MsgBox("Exception Raised in GetDeployList: " & ex.ToString, MsgBoxStyle.OkOnly, "Exception Raised")
            Return False
        End Try
    End Function

    Private Function DeployDTSJob(ByVal JobFileNameWPath As String) As Boolean
        Try
            Dim _oDTSPackage As New DTS.Package2
            Dim _sDTSPkgName As String
            Dim _sDTSPkgDesc As String = ""
            Dim _sDTSPkgCrNm As String
            Dim _sDTSPkgCrDt As String
            Dim _sDTSPkgId As String
            Dim _sDTSPkgVerId As String
            Dim _oDBCmd As System.Data.Common.DbCommand
            Dim _oDataSet As DataSet
            Dim _sSQLCmd As String

            'Load DTS package to get metadata, we do not use this for the 
            'actual deployment due to the way the object model handles 
            'formatting.
            _oDTSPackage.LoadFromStorageFile(JobFileNameWPath, "")

            'Get the bytes of the package, if the package is particularly large
            'this could use a lot of memory
            Dim _oFIO As New IO.FileStream(JobFileNameWPath, IO.FileMode.Open, IO.FileAccess.Read, IO.FileShare.Read)
            Dim _bDTSPkgImage(_oFIO.Length - 1) As Byte

            _oFIO.Read(_bDTSPkgImage, 0, _oFIO.Length)
            _oFIO.Close()
            _oFIO = Nothing

            'Get metadata
            _sDTSPkgName = _oDTSPackage.Name
            _sDTSPkgDesc = _oDTSPackage.Description
            _sDTSPkgCrNm = _oDTSPackage.CreatorName
            _sDTSPkgCrDt = _oDTSPackage.CreationDate
            _sDTSPkgId = _oDTSPackage.PackageID
            _sDTSPkgVerId = _oDTSPackage.VersionID

            _sSQLCmd = "SELECT 'X' FROM msdb.dbo.sysdtspackages WHERE name = '" _
                     & _sDTSPkgName _
                     & "'"

            'Check to see if the object is initialized
            If __oSQL Is Nothing Then
                __oSQL = New Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase(__sConnString)
            End If

            'Remove DTS job if it exists
            _oDataSet = __oSQL.ExecuteDataSet(CommandType.Text, _sSQLCmd)

            If _oDataSet.Tables(0).Rows.Count > 0 Then
                If __sDBTrustedConn = True Then
                    _oDTSPackage.RemoveFromSQLServer(__sDBSrvName, __sDBUserName, __sDBPassword, DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, , , _sDTSPkgName)
                Else
                    _oDTSPackage.RemoveFromSQLServer(__sDBSrvName, __sDBUserName, __sDBPassword, , , , _sDTSPkgName)
                End If
            End If

            _oDTSPackage = Nothing 'Clean up the object, we don't need it anymore.

            If _sDTSPkgDesc Is Nothing Then
                _sDTSPkgDesc = ""
            End If

            _oDBCmd = __oSQL.GetStoredProcCommand("dbo.sp_add_dtspackage")
            __oSQL.AddInParameter(_oDBCmd, "name", DbType.String, _sDTSPkgName)
            __oSQL.AddInParameter(_oDBCmd, "id", DbType.String, _sDTSPkgId)
            __oSQL.AddInParameter(_oDBCmd, "versionid", DbType.String, _sDTSPkgVerId)
            __oSQL.AddInParameter(_oDBCmd, "categoryid", DbType.String, "B8C30002-A282-11d1-B7D9-00C04FB6EFD5")
            __oSQL.AddInParameter(_oDBCmd, "description", DbType.String, _sDTSPkgDesc)
            __oSQL.AddInParameter(_oDBCmd, "owner", DbType.String, _sDTSPkgCrNm)
            __oSQL.AddInParameter(_oDBCmd, "packagedata", DbType.Binary, _bDTSPkgImage)

            __oSQL.ExecuteNonQuery(_oDBCmd)

            Return True
        Catch ex As Exception
            MsgBox("Exception Raised in DeployDTSJob: " & ex.ToString, MsgBoxStyle.OkOnly, "Exception Raised")
            Return False
        End Try
    End Function
#End Region

#Region "Support Routines"
    Private Function ParseDTSDEPLOYLIST(ByVal XMLObj As Chilkat.Xml, ByVal DTSFilePath As String) As Boolean
        Try
            Dim _oXML As Chilkat.Xml

            _oXML = XMLObj.FirstChild

            While Not (_oXML Is Nothing)
                Select Case UCase(_oXML.Tag)
                    Case "DTSJOB"
                        __oNCLDTSJobs.Add(DTSFilePath, Trim(_oXML.Content))
                End Select

                _oXML = _oXML.NextSibling()
            End While

            Return True
        Catch ex As Exception
            MsgBox("Exception Raised in ParseDTSDEPLOYLIST: " & ex.ToString, MsgBoxStyle.OkOnly, "Exception Raised")
            Return False
        End Try
    End Function

    Private Function ValidateDTSFileList() As Boolean
        Dim _fOk As Boolean
        Dim _fReturn As Boolean = True

        Try
            Dim _oNCLDTSJob As NCLDTSJob

            lbMessage.Items.Add("Validating Manifest...")

            For Each _oNCLDTSJob In __oNCLDTSJobs
                _fOk = FileExistChk(_oNCLDTSJob.DTSFilePath, CondFileName(_oNCLDTSJob.DTSFileName))

                If _fOk = False Then
                    lbMessage.Items.Add("DTS Job File: [" & _oNCLDTSJob.DTSFileName & "] does not exist at expected location with the manifest.")
                    _fReturn = False
                End If
            Next
            Return _fReturn
        Catch ex As Exception
            MsgBox("Exception Raised in DeployList: " & ex.ToString, MsgBoxStyle.OkOnly, "Exception Raised")
            Return False
        End Try
    End Function

    Private Function TestConnection() As String
        Try
            Dim _oDataSet As DataSet
            Dim _oDataRow As DataRow
            Dim _sSQLCmd As String

            InitDeploy()

            'Check to see if the object is initialized
            If __oSQL Is Nothing Then
                __oSQL = New Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase(__sConnString)
            End If

            'Build Command
            _sSQLCmd = "SELECT SrvName = @@SERVERNAME"

            'Get Data Set 
            _oDataSet = __oSQL.ExecuteDataSet(CommandType.Text, _sSQLCmd)

            If _oDataSet Is Nothing Then
                Return Nothing
            Else
                _oDataRow = _oDataSet.Tables(0).Rows(0)
                Return Trim(_oDataRow.Item("SrvName"))
            End If
        Catch ex As Exception
            'MsgBox("Exception Raised in TestConnection: " & ex.ToString, MsgBoxStyle.OKOnly, "Exception Raised")
            __sMessage = ex.Message.Trim
            Return Nothing
        End Try
    End Function

    Private Function InitDeploy() As Boolean
        Try
            __sDBSrvName = txtSrvName.Text.Trim
            __sDBUserName = txtUserName.Text.Trim
            __sDBPassword = txtPassword.Text.Trim
            __sDBTrustedConn = chkTrusted.Checked

            __sConnString = BuildConnectionString()

            Return True
        Catch ex As Exception
            'MsgBox("Exception Raised in InitDeploy: " & ex.ToString, MsgBoxStyle.OkOnly, "Exception Raised")
            __sMessage = ex.Message.Trim
        End Try

        Return True
    End Function

    Private Function BuildConnectionString() As String
        Dim _sConnString As String

        If chkTrusted.Checked = False Then
            _sConnString = "Password=" & txtPassword.Text.Trim _
                         & ";Persist Security Info=True;" _
                         & "User ID=" & txtUserName.Text.Trim _
                         & ";Initial Catalog=msdb;" _
                         & "Data Source=" & txtSrvName.Text.Trim
        Else
            _sConnString = "Integrated Security=SSPI" _
                         & ";Persist Security Info=True" _
                         & ";Initial Catalog=msdb;" _
                         & "Data Source=" & txtSrvName.Text.Trim
        End If

        Return _sConnString
    End Function

    Private Function CondFilePath(ByVal FilePath As String) As String
        FilePath = Trim(FilePath)

        If FilePath.Substring(0, 1) = "\" Then
            Return FilePath
        Else
            Return FilePath & "\"
        End If
    End Function

    Private Function CondFileName(ByVal FileName As String) As String
        FileName = FileName.Trim
        FileName = FileName.Replace("\", "_0_")
        FileName = FileName.Replace(":", "_1_")
        FileName = FileName.Replace("|", "_2_")
        FileName = FileName.Replace("*", "_3_")
        FileName = FileName.Replace("+", "_4_")
        FileName = FileName.Replace(Chr(34), "_9_")

        Return FileName
    End Function

    Private Function FileExistChk(ByVal FilePath As String, ByVal FileName As String) As Boolean
        Try
            Dim _oDirectory As System.IO.DirectoryInfo
            Dim _oFIForDir As System.IO.FileInfo()

            _oDirectory = New System.IO.DirectoryInfo(FilePath)
            _oFIForDir = _oDirectory.GetFiles(FileName)

            If _oFIForDir.Length > 0 Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Return False
        End Try
    End Function
#End Region
End Class
